<?php

class SqlEngineCore {

    private static $instance = null;
    private $connection = null;

    /**
     * SqlEngineCore implements the singletone pattern
     * @return SqlEngineCore
     */
    public static function get() {
        if (self::$instance == null)
            self::$instance = new SqlEngineCore();

        return self::$instance;
    }

    public function getConnection($connection_string = null) {
        if (is_null($connection_string))
            $connection_string = sfConfig::get('app_db_connection_string');

        if (is_null($this->connection)) {
            try {
                $this->connection = new PDO($connection_string, sfConfig::get('app_db_username'), sfConfig::get('app_db_password'));
            }
            catch (PDOException $e) {
                $message = $e->getMessage();
                throw new Exception('Unable to connect to the database: ' . $message);
            }
        }

        return $this->connection;
    }

    public function getLastInsertId() {
        $connection = SqlEngineCore::get()->getConnection();

        $query = "SELECT last_insert_id() AS id";

        $result = $this->executeSql($query, array(), true);

        return $result['id'];
    }

    public function executeSql($query, $params = array(), $fetch_first = false, $debug = false) {    
        $connection = $this->getConnection();
        
        $statement = $connection->prepare($query);
        if (false === $statement)
            throw new Exception ('Error while preparing query: ' . $query . ' with data: ' . var_dump($params));

        /* bind the parameters */
        foreach ($params as $param_name => $param_value) {
            if (!is_array($param_value)) {
                if ('NOW()' != $param_value)
                    $statement->bindValue($param_name, $param_value, PDO::PARAM_STR);
            }
            else {
                $statement->bindValue($param_name, $param_value['value'], $param_value['type']);
            }
        }

        /* execute the statement */
        $result = $statement->execute();

        if (false !== $result) {
            if ($fetch_first) {
                $result = $statement->fetch(PDO::FETCH_ASSOC);
            }
            else {
                $result = $statement->fetchAll(PDO::FETCH_ASSOC);
            }

            if (false === $result) {
//                return null;
//                var_dump($statement->errorInfo());die();
//                throw new Exception ('Error while fetching results for query: ' . $query);
            }

            return $result;
        }
        else {
            var_dump($statement->errorInfo());die();
            return null;
            throw new Exception ('Error while executing query: ' . $query);
        }
    }

    public function insert($data) {
        $query = "INSERT INTO " . $data['table'] . " (";
        foreach ($data['columns'] as $column_name => $column_value) {
            $query .= $column_name . ", ";
        }

        /* delete the extra comma and space */
        $query = substr($query, 0, strlen($query) - 2);

        $query .= ') VALUES (';

        foreach ($data['columns'] as $column_name => $column_value) {
            if ('NOW()' == $column_value)
                $query .= 'NOW()' . ', ';
            else
                $query .= ':' . $column_name . ', ';
        }

        /* delete the extra comma and space */
        $query = substr($query, 0, strlen($query) - 2);

        $query .= ')';

        return $this->executeSql($query, $data['columns']);
    }

    public function update($data) {
        $query = "UPDATE " . $data['table'] . " SET ";
        $query_debug = $query;
        foreach ($data['columns'] as $column_name => $column_value) {
            if ('NOW()' == $column_value) {
                $query .= $column_name . " = NOW()" . ', ';
            }
            else {
                $query .= $column_name . ' = :' . $column_name . ', ';
            }

            $query_debug .= $column_name . " = '" . $column_value . "', ";
        }

        /* delete the extra comma and space */
        $query = substr($query, 0, strlen($query) - 2);
        $query_debug = substr($query_debug, 0, strlen($query_debug) - 2);

        $query .= " WHERE ";
        $query_debug .= " WHERE ";
        /* add the where conditions, if any, all of them with AND */
        foreach ($data['where'] as $where_name => $where_value) {
            $query .= $where_name . " = :" . $where_name . " AND ";
            $query_debug .= $where_name . " = '" . $where_value . "' AND ";
        }

        /* delete the extra " AND " */
        $query = substr($query, 0, strlen($query) - strlen(' AND '));
        $query_debug = substr($query_debug, 0, strlen($query_debug) - strlen(' AND '));

//        var_dump($query_debug);die();
//        var_dump($query);die();

        return $this->executeSql($query, array_merge($data['columns'], $data['where']),false,true);
    }

    public function delete($data) {
        $query = "DELETE FROM " . $data['table'] . " ";

        $query .= " WHERE ";
        /* add the where conditions, if any, all of them with AND */
        foreach ($data['where'] as $where_name => $where_value) {
            $query .= $where_name . " = :" . $where_name . " AND ";
        }

        /* delete the extra " AND " */
        $query = substr($query, 0, strlen($query) - strlen(' AND '));

        return $this->executeSql($query, $data['where']);
    }
}